﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace SlamDunk.CMS.DAL
{
    /// <summary>
    /// Photo数据访问层类（手写部分类）
    /// </summary>
    public partial class PhotoDal : IDAL.IPhotoDal
    {
        #region 01.根据相册Id查询图片记录条数
        /// <summary>
        /// 根据相册Id查询图片记录条数
        /// </summary>
        /// <param name="albumId">相册Id</param>
        /// <returns>符合条件的图片记录条数</returns>
        public int GetCount(int albumId)
        {
            string sql = "SELECT COUNT(*) FROM dbo.Photos WHERE DelFlag = 0";

            if (albumId != 0)
            {
                sql = string.Format("{0} AND AlbumId = {1}", sql, albumId);
            }

            return (int)DbHelper.ExecuteScalar(sql);
        }
        #endregion

        #region 02.根据相册Id分页查询图片列表
        /// <summary>
        /// 根据相册Id分页查询图片列表
        /// </summary>
        /// <param name="start">起始行</param>
        /// <param name="end">结束行</param>
        /// <param name="albumId">相册Id</param>
        /// <returns>图片列表</returns>
        public List<Model.Photo> GetModelList(int start, int end, int albumId)
        {
            List<Model.Photo> list = new List<Model.Photo>();
            string sql = "SELECT *, ROW_NUMBER() OVER(ORDER BY AlbumId ASC, Sort DESC, AddTime DESC) AS RowIndex  FROM dbo.Photos WHERE DelFlag = 0";

            #region 非空校验
            if (albumId != 0)
            {
                sql = string.Format("{0} AND AlbumId = {1}", sql, albumId);
            }
            #endregion

            //分页处理
            sql = string.Format("SELECT * FROM ({0}) AS T WHERE T.RowIndex >= @Start AND T.RowIndex <= @End", sql);

            SqlParameter[] args = new SqlParameter[] {
                new SqlParameter("@Start", start),
                new SqlParameter("@End", end)
            };

            using (SqlDataReader reader = DbHelper.ExecuteReader(sql, args))
            {
                while (reader.Read())
                {
                    list.Add(ToModel(reader));
                }
            }
            return list;
        }
        #endregion

        #region 03.根据相册Id查询图片列表
        /// <summary>
        /// 根据相册Id查询图片列表
        /// </summary>
        /// <param name="albumId">相册Id</param>
        /// <returns>图片列表</returns>
        public List<Model.Photo> GetModelList(int albumId)
        {
            List<Model.Photo> list = new List<Model.Photo>();
            string sql = "SELECT * FROM dbo.Photos WHERE DelFlag = 0 AND AlbumId = @AlbumId ORDER BY AlbumId ASC, Sort DESC, AddTime DESC";

            using (SqlDataReader reader = DbHelper.ExecuteReader(sql, new SqlParameter("@AlbumId", albumId)))
            {
                while (reader.Read())
                {
                    list.Add(ToModel(reader));
                }
            }
            return list;
        }
        #endregion
    }
}
